rm(list=ls())

library(here)
here::here()  # points to project root


library(tidyverse)

crosswalk <- read_csv("science_accounts_crosswalk.csv")

contracts <- read_csv("usa_spending_science_contracts_fy2019.csv")

awards <- read_csv("usa_spending_science_assistance_fy2019.csv")

library(lubridate)

contracts$performance_duration <- difftime(ymd(str_sub(contracts$period_of_performance_current_end_date, 1, 10)),
         ymd(str_sub(contracts$period_of_performance_start_date, 1, 10)), units = "weeks")/52

awards$performance_duration <- difftime(ymd(str_sub(awards$period_of_performance_current_end_date, 1, 10)),
                                           ymd(str_sub(awards$period_of_performance_start_date, 1, 10)), units = "weeks")/52


awards <- awards %>% mutate(state_controlled_institution_of_higher_learning = grepl("PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION", business_types_description),
                  private_university_or_college = grepl("PRIVATE INSTITUTION OF HIGHER EDUCATION", business_types_description),
                  nonprofit = grepl("NONPROFIT", business_types_description),
                  business = grepl("BUSINESS", business_types_description), 
                  government = grepl("GOVERNMENT", business_types_description),
                  other = case_when(state_controlled_institution_of_higher_learning == F &
                                      private_university_or_college == F &
                                      nonprofit == F &
                                      business == F &
                                      government == F ~ T,
                                    TRUE ~ F))


contracts <- contracts %>% mutate(business = case_when(corporate_entity_not_tax_exempt == T|
                                                         alaskan_native_corporation_owned_firm == T |
                                                         american_indian_owned_business == T |
                                                         native_hawaiian_organization_owned_firm == T  |  tribally_owned_firm == T |  veteran_owned_business == T |
                                                         service_disabled_veteran_owned_business == T |   woman_owned_business == T | women_owned_small_business == T | economically_disadvantaged_women_owned_small_business == T|     
                                                         joint_venture_women_owned_small_business == T | joint_venture_economic_disadvantaged_women_owned_small_bus == T | minority_owned_business == T |
                                                         asian_pacific_american_owned_business == T |black_american_owned_business == T | hispanic_american_owned_business == T |
                                                         native_american_owned_business == T | other_minority_owned_business == T |  emerging_small_business == T |  community_developed_corporation_owned_firm == T|
                                                         labor_surplus_area_firm == T| partnership_or_limited_liability_partnership == T | sole_proprietorship == T| limited_liability_corporation == T|
                                                         self_certified_small_disadvantaged_business == T| small_disadvantaged_business == T| for_profit_organization == T | subchapter_scorporation == T ~ T, 
                                                       TRUE ~ F))


contracts <- contracts %>% mutate(government = case_when(us_federal_government == T|
                                                           us_state_government == T |  us_local_government == T | city_local_government == T |
                                                           county_local_government == T | inter_municipal_local_government == T | local_government_owned == T |
                                                           municipality_local_government == T | school_district_local_government == T | township_local_government == T |
                                                           us_tribal_government  == T | us_government_entity == T ~ T,
                                                         TRUE ~ F))

contracts <- contracts %>% mutate(nonprofit = case_when(nonprofit_organization == T |other_not_for_profit_organization == T | foundation == T | international_organization == T ~ T,
                                                        T ~ F))




####Contracts Aggregation
group1 <- contracts %>% group_by(state_controlled_institution_of_higher_learning) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T), 
                                                              current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                              potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
  filter(state_controlled_institution_of_higher_learning == T) %>% mutate(type = "state_controlled_institution_of_higher_learning") %>% dplyr::select(type, total_obligated_amount)

group2 <- contracts %>% group_by(private_university_or_college) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T), 
                                                                                      current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                                                      potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
  filter(private_university_or_college == T) %>% mutate(type = "private_university_or_college") %>% dplyr::select(type, total_obligated_amount)


group3 <- contracts  %>% filter(nonprofit == T & state_controlled_institution_of_higher_learning == F & private_university_or_college == F) %>%
  summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T), 
                                                              current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                              potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
   mutate(type = "nonprofit") %>% dplyr::select(type, total_obligated_amount)



group4 <- contracts %>% filter(business == T) %>%
  summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T ), 
                                                                      current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                                      potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
 mutate(type = "business") %>% dplyr::select(type, total_obligated_amount)


group5 <- contracts %>% filter(government == T & state_controlled_institution_of_higher_learning == F)  %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T), 
                                                                                current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                                                potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
   mutate(type = "government") %>% dplyr::select(type, total_obligated_amount)


group6 <- contracts %>% filter(state_controlled_institution_of_higher_learning == F &
                                 private_university_or_college == F &
                                 nonprofit_organization == F &
                                 business == F &
                                 government == F) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T), 
                                                                    current_total_value_of_award = sum(current_total_value_of_award, na.rm = T),
                                                                    potential_total_value_of_award = sum(potential_total_value_of_award, na.rm = T)) %>% 
                        mutate(type = "other") %>% dplyr::select(type, total_obligated_amount)




contracts_agg <- bind_rows(group1, group2, group3, group4, group5, group6) %>% mutate(funding = "contracts")



##### awards agg

group1 <- awards  %>% filter(state_controlled_institution_of_higher_learning == T) %>%
  summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
   mutate(type = "state_controlled_institution_of_higher_learning") %>% dplyr::select(type, total_obligated_amount)

group2 <- awards %>% filter(private_university_or_college == T) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
   mutate(type = "private_university_or_college") %>% dplyr::select(type, total_obligated_amount)


group3 <- awards %>% filter(nonprofit == T & private_university_or_college == F & state_controlled_institution_of_higher_learning == F)  %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
   mutate(type = "nonprofit") %>% dplyr::select(type, total_obligated_amount)



group4 <- awards %>%  filter(business == T) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
   mutate(type = "business") %>% dplyr::select(type, total_obligated_amount)


group5 <- awards %>% filter(government == T & private_university_or_college == F & state_controlled_institution_of_higher_learning == F) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
  mutate(type = "government") %>% dplyr::select(type, total_obligated_amount)


group6 <- awards %>% group_by(other) %>% summarise(total_obligated_amount = sum(total_obligated_amount, na.rm = T)) %>% 
  filter(other == T) %>% mutate(type = "other") %>% dplyr::select(type, total_obligated_amount)



awards_agg <- bind_rows(group1, group2, group3, group4, group5, group6) %>% mutate(funding = "awards")
contracts_agg

spending <- bind_rows(awards_agg, contracts_agg)
spending$type <- fct_reorder(spending$type, spending$total_obligated_amount, "sum") %>% fct_rev()
sptop <- ggplot(spending, aes(x=type, y= total_obligated_amount/1000000000, group = funding, fill = funding)) +
  geom_bar(stat="identity", position = "dodge") +
  scale_y_continuous("Total Obligations\n(in Billions of 2019 USD)") + scale_fill_brewer("Type of Funding", palette = "Dark2", labels = c("Awards", "Contracts")) +
  theme_minimal() + scale_x_discrete("", labels =c("Business", "Other", "Nonprofit", "State Institution\nof Higher Learning", "Government", "Private Institution\nof Higher Learning")) +theme(legend.position = "top")

ggsave("FigS2A.pdf", sptop, width = 6.5, height = 5)

